import hashlib
from pathlib import Path
from utils.db import db


def init_database():
    """初始化数据库"""
    # 读取数据库架构文件
    schema_path = Path(__file__).parent.parent.parent / 'database' / 'schema.sql'

    # 检查是否需要迁移
    need_migration = False
    try:
        # 检查 display_name 列是否存在
        sql = "SELECT display_name FROM users LIMIT 1"
        db.fetch_one(sql)
    except:
        need_migration = True

    if need_migration:
        # 备份现有数据
        users_backup = []
        try:
            users_backup = db.fetch_all("SELECT username, password, role FROM users")
        except:
            pass

        # 删除现有表
        db.execute("DROP TABLE IF EXISTS users")

        # 重新创建表
        with open(schema_path, 'r', encoding='utf-8') as f:
            schema_sql = f.read()
            statements = schema_sql.split(';')
            for statement in statements:
                if statement.strip():
                    db.execute(statement)

        # 恢复备份的数据
        for user in users_backup:
            sql = """
            INSERT INTO users (username, password, role, display_name)
            VALUES (?, ?, ?, ?)
            """
            db.execute(sql, (user['username'], user['password'], user['role'], None))

    # 创建默认管理员账户（如果不存在）
    admin_username = "admin"
    admin_password = hashlib.md5("admin123".encode()).hexdigest()

    # 检查管理员是否已存在
    sql = "SELECT id FROM users WHERE username = ?"
    if not db.fetch_one(sql, (admin_username,)):
        # 创建管理员账户
        sql = """
        INSERT INTO users (username, password, role, display_name)
        VALUES (?, ?, 'admin', '系统管理员')
        """
        db.execute(sql, (admin_username, admin_password))

    # 创建默认服务项目（如果不存在）
    default_services = [
        ("洗剪吹", 68.00, 45, "基础洗剪吹服务"),
        ("染发", 198.00, 120, "专业染发服务"),
        ("烫发", 298.00, 150, "专业烫发服务"),
        ("护理", 158.00, 60, "深层滋养护理"),
    ]

    for name, price, duration, description in default_services:
        sql = "SELECT id FROM services WHERE name = ?"
        if not db.fetch_one(sql, (name,)):
            sql = """
            INSERT INTO services (name, price, duration, description)
            VALUES (?, ?, ?, ?)
            """
            db.execute(sql, (name, price, duration, description))

    # 创建默认员工账户（如果不存在）
    default_staffs = [
        ("staff1", "123456", "张美发"),
        ("staff2", "123456", "李美发"),
        ("staff3", "123456", "王美发"),
    ]

    for username, password, display_name in default_staffs:
        sql = "SELECT id FROM users WHERE username = ?"
        if not db.fetch_one(sql, (username,)):
            hashed_password = hashlib.md5(password.encode()).hexdigest()
            sql = """
            INSERT INTO users (username, password, role, display_name)
            VALUES (?, ?, 'staff', ?)
            """
            db.execute(sql, (username, hashed_password, display_name))
